﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class MovePhotoDAL
    {
        //参数
        string conStr = ConfigurationManager.ConnectionStrings["fffff"].ToString();
        public newSampleYPDBEntities DataEntites = new newSampleYPDBEntities();

        #region 获取第一个要移动的照片(用entity)
        /// <summary>
        /// 获取第一个要移动的照片(用entity)
        /// </summary>
        /// <returns></returns>
        public photoInfo GetMovePhoto(int userId)
        {
            try
            {
                //DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins,DataEntites.photoInfo);
                int photoWaitStatus = (int)COM.PhotoStatus.WaitPhotoMove;
                photoInfo photo = DataEntites.photoInfo.Where(p => p.status == photoWaitStatus).FirstOrDefault();
                //photoInfo photo = DataEntites.photoInfo.Where(p => p.status == 4).FirstOrDefault();
                try
                {
                    if (photo == null)
                        return null;
                    photo.status = (int)COM.PhotoStatus.PhotoBingFa;
                    photo.userId = userId;
                    DataEntites.SaveChanges();
                }
                catch (Exception ex2)                  //并发出错
                {
                    if (ex2 is OptimisticConcurrencyException)
                    {
                        DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, photo);
                        //DataEntites.AcceptAllChanges();
                        photo = GetMovePhoto(userId);//
                    }
                    else
                    {
                        throw ex2;
                    }
                }
                if (photo == null)
                    return null;
                else
                {
                    if (!photo.orderInfoReference.IsLoaded)
                        photo.orderInfoReference.Load();
                    return photo;
                }
            }
            catch (Exception ex)
            {
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.photoInfo);
                throw ex;
            }
        }
        #endregion

        #region 获取第一张要移动的照片(传统方式)
        /// <summary>
        /// 获取第一张要移动的照片(传统方式)
        /// </summary>
        /// <returns></returns>
        public DataTable GetMovePhotoCommon(int userId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                int noMoveStatus = (int)COM.PhotoStatus.WaitPhotoMove;
                int bingFaStatus = (int)COM.PhotoStatus.PhotoBingFa;
                SqlCommand cmd = new SqlCommand("Proc_GetNoMovePhoto", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("noMoveStatus", noMoveStatus));
                cmd.Parameters.Add(new SqlParameter("bingFaStatus", bingFaStatus));
                cmd.Parameters.Add(new SqlParameter("userId", userId));
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 一个产品中是否还有没有移动的照片存在
        /// <summary>
        /// 一个产品中是否还有没有移动的照片存在
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool IsHavePhotoNoMove(orderInfo info)
        {
            try
            {
                int photoWaitStatus = (int)COM.PhotoStatus.WaitPhotoMove;
                int photoBingFaStatus = (int)COM.PhotoStatus.PhotoBingFa;
                photoInfo photo = DataEntites.photoInfo.Where(p => p.orderInfo.OrderProductId == info.OrderProductId && (p.status == photoWaitStatus || p.status == photoBingFaStatus)).FirstOrDefault();
                if (photo == null)
                    return false;
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 一个订单中是否还有没有移动的产品
        /// <summary>
        /// 一个订单中是否还有没有移动的产品
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public bool IsHaveProductNoMove(Tb_OrderBag order)
        {
            try
            {
                int infoWaitStatus = (int)COM.OrderInfoStatus.WaitPhotoMove;
                orderInfo info = DataEntites.orderInfo.Where(p => p.Tb_OrderBag.OrderBagId == order.OrderBagId && p.status == infoWaitStatus).FirstOrDefault();
                if (info == null)
                    return false;
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取所有还没有移动完的订单
        /// <summary>
        /// 获取所有还没有移动完的订单
        /// </summary>
        /// <returns></returns>
        public DataTable GetNoMoveOrders()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                //int orderWaitStatus = (int)COM.OrderBagStatus.WaitPhotoMove;
                //List<Tb_OrderBag> ListOrder = DataEntites.Tb_OrderBag.Where(p => p.Status == orderWaitStatus).ToList();
                //return ListOrder;
                int photoMoveStatus = (int)COM.PhotoStatus.WaitPhotoMove;
                int photoUnExist = (int)COM.PhotoStatus.PhotoNotExist;
                int orderMoveStatus = (int)COM.OrderBagStatus.WaitPhotoMove;
                string sql = "";
                sql += "SELECT Tb_OrderBag_1.OrderBagId, Tb_OrderBag_1.BagName, Tb_OrderBag_1.OrderTime, COUNT(photoInfo_1.photoId) AS TotalPhotoNum,";
                sql += "  (SELECT COUNT(photoInfo.photoId) AS Expr1 FROM photoInfo INNER JOIN";
                sql += " orderInfo ON photoInfo.OrderProductId = orderInfo.OrderProductId INNER JOIN";
                sql += " Tb_OrderBag ON orderInfo.OrderBagId = Tb_OrderBag.OrderBagId";
                sql += " WHERE (photoInfo.status = " + photoMoveStatus + " or photoInfo.status = " + photoUnExist + ") AND (Tb_OrderBag.OrderBagId = Tb_OrderBag_1.OrderBagId)) AS MovePhotoNum";
                sql += " FROM Tb_OrderBag AS Tb_OrderBag_1 INNER JOIN";
                sql += " orderInfo AS orderInfo_1 ON Tb_OrderBag_1.OrderBagId = orderInfo_1.OrderBagId INNER JOIN ";
                sql += " photoInfo AS photoInfo_1 ON orderInfo_1.OrderProductId = photoInfo_1.OrderProductId";
                sql += " where orderInfo_1.ComputerName is null or orderInfo_1.ComputerName =0 ";
                sql += " GROUP BY Tb_OrderBag_1.OrderBagId, Tb_OrderBag_1.BagName, Tb_OrderBag_1.Status, Tb_OrderBag_1.OrderTime";
                sql += " HAVING (Tb_OrderBag_1.Status = " + orderMoveStatus + ")";
                //StringBuilder sql = new StringBuilder();
                //sql.Append("SELECT     Tb_OrderBag_1.OrderBagId, Tb_OrderBag_1.BagName, Tb_OrderBag_1.OrderTime, COUNT(photoInfo_1.photoId) AS TotalPhotoNum,");
                //sql.Append("  (SELECT COUNT(photoInfo.photoId) AS Expr1 FROM photoInfo INNER JOIN");
                //sql.Append(" orderInfo ON photoInfo.OrderProductId = orderInfo.OrderProductId INNER JOIN");
                //sql.Append(" Tb_OrderBag ON orderInfo.OrderBagId = Tb_OrderBag.OrderBagId");
                //sql.Append(" WHERE (photoInfo.status = {0}) AND (Tb_OrderBag.OrderBagId = Tb_OrderBag_1.OrderBagId)) AS MovePhotoNum");
                //sql.Append(" FROM Tb_OrderBag AS Tb_OrderBag_1 INNER JOIN");
                //sql.Append(" orderInfo AS orderInfo_1 ON Tb_OrderBag_1.OrderBagId = orderInfo_1.OrderBagId INNER JOIN");
                //sql.Append(" photoInfo AS photoInfo_1 ON orderInfo_1.OrderProductId = photoInfo_1.OrderProductId");
                //sql.Append(" GROUP BY Tb_OrderBag_1.OrderBagId, Tb_OrderBag_1.BagName, Tb_OrderBag_1.Status, Tb_OrderBag_1.OrderTime");
                //sql.Append(" HAVING (Tb_OrderBag_1.Status = {1}");
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取订单内所有的照片数，和还未移动的照片数
        /// <summary>
        /// 获取订单内所有的照片数，和还未移动的照片数
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public List<int> GetPhotoNums(int orderId)
        {
            try
            {
                //订单内的照片总量
                int allPhotos = DataEntites.photoInfo.Where(p => p.orderInfo.Tb_OrderBag.OrderBagId == orderId).Count();   //集合为空会不会出错?
                int photoWaitStatus = (int)COM.PhotoStatus.WaitPhotoMove;
                int NoMovePhotos = DataEntites.photoInfo.Where(p => p.orderInfo.Tb_OrderBag.OrderBagId == orderId && p.status == photoWaitStatus).Count();
                List<int> photoNums = new List<int>();
                photoNums.Add(allPhotos);
                photoNums.Add(NoMovePhotos);
                return photoNums;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 保存修改
        /// <summary>
        /// 保存修改
        /// </summary>
        public void SaveData()
        {
            try
            {
                DataEntites.SaveChanges();
            }
            catch
            {
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.Tb_OrderBag);
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.orderInfo);
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.photoInfo);
            }
        }
        #endregion

        #region 将并发状态改回照片等待移动状态
        /// <summary>
        /// 将并发状态改回照片等待移动状态
        /// </summary>
        /// <param name="userId"></param>
        public void ModifyBack(int userId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                int bingFaStatus = (int)COM.PhotoStatus.PhotoBingFa;
                /*
                IQueryable<photoInfo> PhotoList = DataEntites.photoInfo.Where(p => p.status == bingFaStatus && p.userId == userId);
                foreach (photoInfo photo in PhotoList)
                {
                    photo.status = (int)COM.PhotoStatus.WaitPhotoMove;
                }
                try
                {
                    DataEntites.SaveChanges();
                }
                catch (Exception exx)
                {
                    DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, PhotoList);      //并发出错，按数据库原始数据刷新
                    throw exx;
                }
                */
                //传统模式
                int waitMoveStatus = (int)COM.PhotoStatus.WaitPhotoMove;
                string sql = "update photoInfo set status=" + waitMoveStatus + " where status=" + bingFaStatus + " and userId=" + userId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改不存在的照片状态为“PhotoNotExist(照片不存在)”
        /// <summary>
        /// 修改不存在的照片状态为“PhotoNotExist(照片不存在)”
        /// </summary>
        /// <param name="photoId"></param>
        public void ModifyPhotoStatusToPhotoNotExist(int photoId)
        {
            try
            {
                int PhotoNotExistStatus = (int)COM.PhotoStatus.PhotoNotExist;       //照片不存在
                photoInfo photo = DataEntites.photoInfo.Where(p => p.photoId == photoId).FirstOrDefault();
                if (photo == null)
                    return;
                photo.status = PhotoNotExistStatus;
                try
                {
                    DataEntites.SaveChanges();
                }
                catch (Exception exx)
                {
                    DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, photo);
                    throw exx;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 将等待照片移动的状态改为原始状态
        /// <summary>
        /// 将等待照片移动的状态改为原始状态
        /// </summary>
        public void ModifyPhotoNotExistToPhotoWaitMove()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                int waitMove = (int)COM.PhotoStatus.WaitPhotoMove;
                int NotExist = (int)COM.PhotoStatus.PhotoNotExist;
                string sql = "update photoInfo set status=" + waitMove + " where status=" + NotExist;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 调用Context的AcceptAllChanges
        /// <summary>
        /// 调用Context的AcceptAllChanges
        /// </summary>
        public void AcceptChanges()
        {
            try
            {
                DataEntites.AcceptAllChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 调用Context的DeleteObject
        /// <summary>
        /// 调用Context的DeleteObject
        /// </summary>
        /// <param name="obj"></param>
        public void Delete(object obj)
        {
            try
            {
                DataEntites.DeleteObject(obj);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 刷新对象
        /// <summary>
        /// 刷新对象
        /// </summary>
        /// <param name="obj"></param>
        public void RefreshOrderOrderInfoPhotoInfo()
        {
            try
            {
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.Tb_OrderBag);
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.orderInfo);
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.photoInfo);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        public void RefreshObject(object obj)
        {
            try
            {
                DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, obj);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region 用存储过程修改照片状态、产品状态、订单状态
        /// <summary>
        /// 用存储过程修改照片状态、产品状态、订单状态
        /// </summary>
        /// <param name="photoId"></param>
        public int ChangeStatusByStoreProcedure(int photoId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                int photoMoveStatus = (int)COM.PhotoStatus.WaitPhotoMove;
                int photoBingFa = (int)COM.PhotoStatus.PhotoBingFa;
                int photoNotExist = (int)COM.PhotoStatus.PhotoNotExist;
                int noDistributePrinter = (int)COM.PhotoStatus.NoDistributePrinter;
                int orderInfoMovePhoto = (int)COM.OrderInfoStatus.WaitPhotoMove;
                int orderInfoStart = (int)COM.OrderInfoStatus.Doing;
                int orderBagDoing = (int)COM.OrderBagStatus.Doing;
                SqlCommand cmd = new SqlCommand("Proc_ChangPhotoStatus", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("photoId", photoId));
                cmd.Parameters.Add(new SqlParameter("@photoMoveStatus", photoMoveStatus));
                cmd.Parameters.Add(new SqlParameter("@bingFaStatus", photoBingFa));
                cmd.Parameters.Add(new SqlParameter("@noDistributePrinter", noDistributePrinter));
                cmd.Parameters.Add(new SqlParameter("@orderInfoWaitPhotoMove", orderInfoMovePhoto));
                cmd.Parameters.Add(new SqlParameter("@photoNotExist", photoNotExist));
                cmd.Parameters.Add(new SqlParameter("@orderInfoStart", orderInfoStart));
                cmd.Parameters.Add(new SqlParameter("@orderBagDoing", orderBagDoing));
                con.Open();
                int r = Convert.ToInt32(cmd.ExecuteScalar());
                return r;           //1还有未移动的照片 2还有处于移动状态的产品 3订单状态已修改
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
    }
}
